{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Task-05 Mysql"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "USE shop;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 5.1 窗口函数\r\n",
    "\r\n",
    "* ## 搞明白关键字 PARTITON BY 和 ORDER BY 的作用。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "--模板\r\n",
    "-- <窗口函数> OVER ([PARTITION BY <列名>]\r\n",
    "--                      ORDER BY <排序用列名>)  "
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- partition by\r\n",
    "SELECT product_name\r\n",
    "       ,product_type\r\n",
    "       ,sale_price\r\n",
    "       ,RANK() OVER (PARTITION BY product_type\r\n",
    "                         ORDER BY sale_price)AS ranking\r\n",
    "  FROM product ;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT  product_name\r\n",
    "       ,product_type\r\n",
    "       ,sale_price\r\n",
    "       ,RANK() OVER (ORDER BY sale_price) AS ranking\r\n",
    "       ,DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking\r\n",
    "       ,ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num\r\n",
    "  FROM product  ;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "* ## RANK函数\r\n",
    "计算排序时，如果存在相同位次的记录，则会跳过之后的位次。\r\n",
    "\r\n",
    "例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 5.2.2 聚合函数在窗口函数上的使用"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT  product_id\r\n",
    "       ,product_name\r\n",
    "       ,sale_price\r\n",
    "       ,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum\r\n",
    "       ,AVG(sale_price) OVER (ORDER BY product_id) AS current_avg  \r\n",
    "  FROM product;  "
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 5.3 窗口函数的的应用 - 计算移动平均\r\n",
    "\r\n",
    "## 即前几行，或者后几行的，对此窗口上的数据函数处理"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- <窗口函数> OVER (ORDER BY <排序用列名>\r\n",
    "--                  ROWS n PRECEDING )  \r\n",
    "                 \r\n",
    "-- <窗口函数> OVER (ORDER BY <排序用列名>\r\n",
    "                --  ROWS BETWEEN n PRECEDING AND n FOLLOWING)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "* ## PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行\r\n",
    "\r\n",
    "* ## FOLLOWING（“之后”）， 将框架指定为 “截止到之后 n 行”，加上自身行\r\n",
    "\r\n",
    "* ## BETWEEN 1 PRECEDING AND 1 FOLLOWING，将框架指定为 “之前1行” + “之后1行” + “自身”"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT  product_id\r\n",
    "       ,product_name\r\n",
    "       ,sale_price\r\n",
    "       ,AVG(sale_price) OVER (ORDER BY product_id\r\n",
    "                               ROWS 2 PRECEDING) AS moving_avg\r\n",
    "       ,AVG(sale_price) OVER (ORDER BY product_id\r\n",
    "                               ROWS BETWEEN 1 PRECEDING \r\n",
    "                                        AND 1 FOLLOWING) AS moving_avg  \r\n",
    "  FROM product  ;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "* ## 原则上，窗口函数只能在SELECT子句中使用。\r\n",
    "\r\n",
    "* ## 窗口函数OVER 中的ORDER BY 子句并不会影响最终结果的排序。\r\n",
    "* 其只是用来决定窗口函数按何种顺序计算。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 5.4 GROUPING运算符"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT  product_type\r\n",
    "       ,regist_date\r\n",
    "       ,SUM(sale_price) AS sum_price\r\n",
    "  FROM product\r\n",
    " GROUP BY product_type, regist_date WITH ROLLUP;  "
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 练习题"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT  product_id\r\n",
    "       ,product_name\r\n",
    "       ,sale_price\r\n",
    "       ,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price\r\n",
    "  FROM product;\r\n",
    "  ---从第一行当本行的价格最大值\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT  product_type\r\n",
    "       ,regist_date\r\n",
    "       ,sale_price\r\n",
    "       ,SUM(sale_price) OVER (ORDER BY product_type) AS sum_price\r\n",
    "  FROM product\r\n",
    " GROUP BY product_type, regist_date  \r\n",
    " ORDER BY regist_date IS NULL,regist_date;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "* ## PARTITION BY仅分组，组输入和单项输出都有\r\n",
    "\r\n",
    "* ## \r\n",
    "\r\n",
    "* ## 窗口函数只能在SELECT子句中使用，语句优先级刚高于select，同时也高于select之后的order by,但其优先级其实很低"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  }
 ],
 "metadata": {
  "orig_nbformat": 4,
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}